//
// Translated by CS2J (http://www.cs2j.com): 2017-1-26 21:44:45
//

package cn.kivensoft.xlsx.helper;

import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;

import cn.kivensoft.xlsx.writer.Cell;
import cn.kivensoft.xlsx.writer.Style;
import cn.kivensoft.xlsx.writer.Workbook;
import cn.kivensoft.xlsx.writer.Worksheet;
import cn.kivensoft.xlsx.writer.Cell.CellType;
import cn.kivensoft.xlsx.writer.Style.Border;
import cn.kivensoft.xlsx.writer.Style.NumberFormat;
import cn.kivensoft.xlsx.writer.Style.Border.StyleValue;
import cn.kivensoft.xlsx.writer.Style.CellXf.HorizontalAlignValue;
import cn.kivensoft.xlsx.writer.Style.Fill.FillType;

/* 使用范例：
		String[] headers = { "姓名", "年龄", "电话", "家庭住址", "余额" };
		float[] widths = { 14f, 12f, 20f, 22f, 10f };
		XlsxExport.setXmlFormatIndent(true);
		XlsxExport xls = new XlsxExport("工作表33");
		xls.setTitle("大标题", new String[] { "查询日期", "打印日期" },
				new int[] { 3, 2 }, headers, widths);

		xls.goToNextRow();
		xls.addNextCell("Kiven", true);
		xls.addNextCell(42);
		xls.addNextCellDateTime(new Date());
		xls.addNextCellDate(new Date());
		xls.addNextCellMoney(3459.8);
		xls.goToNextRow();
		xls.addNextCell("Kiven2", true);
		xls.addNextCell(76);
		xls.addNextCellDateTime(new Date());
		xls.addNextCellDate(new Date());
		xls.addNextCellMoney(4421);
		xls.setFooter("合计", 3, new boolean[] { false, true });

		OutputStream output = new FileOutputStream("z:\\测试.xlsx");
		xls.write(output);
		output.close();
 */
public class XlsxExport {
	private Workbook workbook;
	private Worksheet sheet;
	private int currentCol, currentRow, firstRow; // 当前读写行/列, 记录行起始行
	private Style titleStyle; // 标题样式
	private Style[] subTitleStyles; // 二级标题样式
	private Style headerStyle; // 表头样式
	private Style[] recordStyles; // 表记录样式
	private Style footerStyle; // 表尾合计行样式
	private Calendar _calendar = Calendar.getInstance();

	public static void setXmlFormatIndent(boolean value) {
		Workbook.setXmlFormatIndent(value);
	}

	public Workbook getWorkbook() { return workbook; }
	public Worksheet getSheet() { return sheet; }
	public int getCurrentCol() { return currentCol; }
	public int getCurrentRow() { return currentRow; }
	public int getFirstRow() { return firstRow; }

	public void setCurrentCol(int value) { currentCol = value; }
	public void setCurrentRow(int value) { currentRow = value; }
	public void setFirstRow(int value) { firstRow = value; }

	public XlsxExport(String sheetName) throws Exception {
		workbook = new Workbook(sheetName);
		sheet = workbook.getCurrentWorksheet();

		// 创建边框样式
		Border b = new Border();
		b.topStyle = StyleValue.thin;
		b.bottomStyle = b.topStyle;
		b.leftStyle = b.topStyle;
		b.rightStyle = b.topStyle;

		// 创建日期格式化样式
		NumberFormat fmt = new NumberFormat();
		fmt.number = NumberFormat.FORMAT_CUSTOM;
		fmt.customFormatID = 250;
		fmt.customFormatCode = "yyyy-mm-dd hh:mm:ss";

		//创建一级和二级标题样式
		titleStyle = new Style("title");
		titleStyle.currentFont.bold = true;
		titleStyle.currentFont.size = 24;
		titleStyle.currentCellXf.horizontalAlign = HorizontalAlignValue.center;
		subTitleStyles = new Style[2];
		subTitleStyles[0] = new Style("subTitleLeft");
		subTitleStyles[1] = new Style("subTitleRight");
		subTitleStyles[1].currentCellXf.horizontalAlign = HorizontalAlignValue.right;

		//创建表头样式
		headerStyle = new Style("header");
		headerStyle.currentBorder = b;
		headerStyle.currentFont.bold = true;
		headerStyle.currentFill.setColor("00DCDCDC", FillType.fillColor);
		headerStyle.currentCellXf.horizontalAlign = HorizontalAlignValue.center;

		//创建单元格样式
		int rlen = 10, rharf = rlen >> 1;
		recordStyles = new Style[rlen];
		for (int i = 0; i < rlen; i++) {
			Style rs = new Style("record_" + i);
			rs.currentBorder = b;
			if (i >= rharf)
				rs.currentFill.setColor("00F2F2F2", FillType.fillColor);
			recordStyles[i] = rs;
		}
		recordStyles[1].currentCellXf.horizontalAlign = HorizontalAlignValue.center;
		recordStyles[1 + rharf].currentCellXf = recordStyles[1].currentCellXf;
		recordStyles[2].currentNumberFormat.number = NumberFormat.FORMAT_4;
		recordStyles[2 + rharf].currentNumberFormat = recordStyles[2].currentNumberFormat;
		recordStyles[3].currentNumberFormat = fmt;
		recordStyles[3 + rharf].currentNumberFormat = recordStyles[3].currentNumberFormat;
		recordStyles[4].currentNumberFormat.number = NumberFormat.FORMAT_14;
		recordStyles[4 + rharf].currentNumberFormat = recordStyles[4].currentNumberFormat;

		//创建页脚样式
		footerStyle = new Style("footer");
		footerStyle.currentBorder = b;
		footerStyle.currentFont.bold = true;
		footerStyle.currentFill.setColor("00DCDCDC", FillType.fillColor);
	}

	public void addWorkSheet(String name) throws Exception {
		workbook.addWorksheet(name);
		sheet = workbook.getCurrentWorksheet();
		currentRow = 0;
		currentCol = 0;
		firstRow = 0;
	}

	public void setCurrentWorkSheet(int index) {
		sheet = workbook.setCurrentWorksheet(index);
		currentRow = 0;
		currentCol = 0;
		firstRow = 0;
	}

	public void setTitle(String title, String[] subTitles, int[] subTitleMerges,
			String[] headers, float[] widths) throws Exception {

		if (title == null || title.length() == 0 || headers.length != widths.length)
			throw new Exception("设置标题校验错误！");

		int hlen = headers.length;
		// 设置一级标题
		Cell c = new Cell(title, CellType.DEFAULT, 0, 0);
		c.setStyle(titleStyle, workbook);
		sheet.addCell(c);
		sheet.mergeCells(new Cell.Address(0, 0), new Cell.Address(hlen - 1, 0));
		// 设置二级子标题
		if (subTitles != null) {
			int next_col = 0;
			for (int i = 0, n = subTitles.length; i < n; i++) {
				c = new Cell(subTitles[i], CellType.DEFAULT, next_col, 1);
				c.setStyle(subTitleStyles[i], workbook);
				sheet.addCell(c);
				int cm = subTitleMerges[i];
				if (cm > 1)
					sheet.mergeCells(new Cell.Address(next_col, 1),
							new Cell.Address(next_col + cm - 1, 1));
				next_col += cm;
			}
			currentRow = 1;
		}

		// 设置列头
		++currentRow;
		for (int i = 0; i < hlen; i++) {
			c = new Cell(headers[i], CellType.DEFAULT, i, currentRow);
			c.setStyle(headerStyle, workbook);
			sheet.addCell(c);
			sheet.setColumnWidth(i, widths[i]);
		}

		// 设置第一行记录行位置
		firstRow = currentRow + 1;
	}

	/**
	 * 转到下一行，设置完标题后，在写入每行记录之前调用
	 */
	public void goToNextRow() {
		++currentRow;
		currentCol = 0;
	}

	/**
	 * 在当前行当前列新增一个单元格，新增完成后当前列会自动加1
	 *
	 * @param value 单元格的值
	 * @param isCenter 是否居中显示
	 */
	public void addNextCell(Object value, boolean isCenter) throws Exception {
		Cell c = new Cell(value, CellType.DEFAULT, currentCol++, currentRow);
		int inc = !parityCheck() ? 0 : 5;
		if (isCenter) c.setStyle(recordStyles[1 + inc], workbook);
		else c.setStyle(recordStyles[0 + inc], workbook);
		sheet.addCell(c);
	}

	public void addNextCell(Object value) throws Exception {
		addNextCell(value, false);
	}

	public void addNextCellMoney(Number value) throws Exception {
		Cell c = new Cell(value, CellType.DEFAULT, currentCol++, currentRow);
		int inc = !parityCheck() ? 0 : 5;
		c.setStyle(recordStyles[2 + inc], workbook);
		sheet.addCell(c);
	}

	public void addNextCellDateTime(Date value) throws Exception {
		Cell c = new Cell(value, CellType.DATE, currentCol++, currentRow);
		int inc = !parityCheck() ? 0 : 5;
		c.setStyle(recordStyles[3 + inc], workbook);
		sheet.addCell(c);
	}

	public void addNextCellDate(Date value) throws Exception {
		_calendar.setTime(value);
		_calendar.set(Calendar.HOUR_OF_DAY, 0);
		_calendar.set(Calendar.MINUTE, 0);
		_calendar.set(Calendar.SECOND, 0);
		_calendar.set(Calendar.MILLISECOND, 0);
		Cell c = new Cell(_calendar.getTime(), CellType.DATE, currentCol++, currentRow);
		int inc = !parityCheck() ? 0 : 5;
		c.setStyle(recordStyles[4 + inc], workbook);
		sheet.addCell(c);
	}

	/**
	 * 设置表尾的合计列
	 *
	 * @param title 合计列标题，位于该行首列
	 * @param titleMerge 合计列标题的合并单元格数量
	 * @param footers 剩余单元格的是否计算合计值数组，true为计算，false忽略
	 */
	public void setFooter(String title, int titleMerge, boolean[] footers) throws Exception {
		goToNextRow();
		if (title != null && title.length() > 0) {
			Cell c = new Cell(title, CellType.DEFAULT, 0, currentRow);
			c.setStyle(footerStyle, workbook);
			sheet.addCell(c);
			if (titleMerge > 1) {
				for (int i = 1; i < titleMerge; i++) {
					c = new Cell("", CellType.EMPTY, i, currentRow);
					c.setStyle(footerStyle, workbook);
					sheet.addCell(c);
				}
				sheet.mergeCells(new Cell.Address(0, currentRow),
						new Cell.Address(titleMerge - 1, currentRow));
			}

			currentCol = titleMerge;
		}

		for (int i = 0, n = footers.length; i < n; i++) {
			boolean b = footers[i];
			Cell c;
			if (b) {
				Cell.Address addr1 = new Cell.Address(currentCol + i, firstRow);
				Cell.Address addr2 = new Cell.Address(currentCol + i, currentRow - 1);
				c = new Cell(String.format("SUM(%s:%s)", addr1.getAddress(),
						addr2.getAddress()), CellType.FORMULA, currentCol + i,
						currentRow);
			} else
				c = new Cell("", CellType.EMPTY, currentCol + i, currentRow);
			c.setStyle(footerStyle, workbook);
			sheet.addCell(c);
		}
	}

	public void write(OutputStream stream) throws Exception {
		workbook.write(stream);
	}

	public void save(String fileName) throws Exception {
		workbook.saveAs(fileName);
	}

	// 判断当前记录行是奇数行还是偶数行
	private boolean parityCheck() {
		return ((currentRow - firstRow) & 1) == 0;
	}
}
